Workloads submitted to database management systems (DBMSs) typically consist of multiple transactions, which may consist of multiple structured query language (SQL) queries. While processing a transaction, the database engine records actions performed in a log, which enables enforcement of the Atomicity, Consistency, Isolation, and Durability (ACID) properties of the transaction. Atomicity refers to the ability of a DBMS to guarantee that either all of the tasks of a transaction or none of them are performed. Consistency refers to a DBMS being in a legal or allowed state when a transaction begins and ends. Isolation refers to the ability of an application to make operations in a transaction appear isolated from all other operations. Durability refers to a guarantee that, once a user has been notified of success of a transaction, the transaction will persist and will not be undone. Typically, transactions recorded in the log can be replayed to return the DBMS to its state just prior to a failure.
A transaction can only be deemed committed after being safely included in the transaction log. The transaction log is typically stored in a log space on a data storage device, the size of which is pre-determined or selectable by a user. Upon completion of a transaction and the necessary changes to the data, the database engine frees up the space occupied by the transaction log relating to that particular transaction. However, if a database engine runs out of log space, transactions already performed are cancelled, which results in a waste of computing time and resources. As it is desirable to execute workloads without user or human supervision (e.g., overnight), a need exists to prevent database engines from running out of log space.
A common remedy attempted to avoid running out of log space is to use as large a log space as possible. However, this approach suffers from significant disadvantages. Either the log space size has to be manually configured before each workload or a large portion of storage has to be permanently reserved to provide sufficient log space for the largest conceivable workload. Both of the foregoing are a waste of resources and the very real possibility exists that manual configuration may be forgotten. Furthermore, in the event of a crash, the cost of performing a database rollback with a large transaction log is substantial.
U.S. Pat. No. 6,542,893, entitled “Database sizer for preemptive multitasking operating system”, issued to Quernemoen on Apr. 1, 2003 and is assigned to Unisys Corporation. This document relates to software for sizing relational database management systems (RDBMSs) and, more particularly, to methods for determining the required size for database storage.
U.S. Pat. No. 6,732,124, entitled “Data processing system with mechanism for restoring file systems based on transaction logs”, issued to Koseki, et al. on May 4, 2004 and is assigned to Fujitsu Limited. This document relates to a data processing system which can recover from system failures by using log records to restore the consistency of its file system structure.
U.S. Pat. No. 6,944,711, entitled “Cache management method for storage device”, issued to Mogi, et al. on Sep. 13, 2005 and is assigned to Hitachi, Ltd. This document relates to cache space settings on a storage device in a computer system for running a database management system (DBMS).
Certain existing DBMSs such as Microsoft SQL Server include an “auto grow” feature, which may be used to reduce the potential of running out of transaction log space. If this feature is enabled without imposing any maximum storage space limit, the system will continue growing the log space in accordance with current requirements until no more physical storage space is available, at which point, the transaction/s will fail. Use of the “auto grow” feature with a large storage space limit simply results in the possibility of aborted processing of a relatively larger workload.
Accordingly, a need exists for improved and/or efficient methods, systems and products for efficiently managing transaction log space for scheduling workloads in database management systems.